package com.shlh.saas.controller;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.shlh.saas.common.Desensitization;
import com.shlh.saas.common.Result;
import com.shlh.saas.common.UserContext;
import com.shlh.saas.entity.UserBalance;
import com.shlh.saas.entity.facebook.FacebookMatchRecord;
import com.shlh.saas.model.dto.FacebookQueryDTO;
import com.shlh.saas.entity.facebook.FacebookUser;
import com.shlh.saas.service.UserBalanceService;
import com.shlh.saas.service.WorkbenchStatsService;
import com.shlh.saas.service.facebook.FacebookMatchRecordService;
import com.shlh.saas.service.facebook.FacebookUserService;
import com.shlh.saas.util.ExcelUtils;
import com.shlh.saas.service.ExportRecordService;
import com.shlh.saas.util.FileUtils;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.Date;
import java.nio.charset.StandardCharsets;

/**
 * Facebook数据控制器
 */
@RestController
@RequestMapping("/facebook")
@Slf4j
public class FacebookController {

    @Autowired
    private FacebookUserService facebookUserService;
    
    @Autowired
    private WorkbenchStatsService workbenchStatsService;
    
    @Autowired
    private UserBalanceService userBalanceService;

    @Autowired
    private ExportRecordService exportRecordService;

    @Autowired
    private FacebookMatchRecordService facebookMatchRecordService;

    @Resource
    private Desensitization desensitization;

    // 模板文件路径定义
    private static final String TEMPLATE_DIR = "static/templates/facebook/";
    private static final String COMPANY_NAME_TO_EMAIL = "company_name_to_email.xlsx";
    private static final String COMPANY_NAME_TO_PHONE = "company_name_to_phone.xlsx";
    private static final String NAME_EMAIL_TO_PHONE = "name_email_to_phone.xlsx";
    private static final String PHONE_NAME_TO_EMAIL = "phone_name_to_email.xlsx";
    private static final String DEFAULT_TEMPLATE = "default_template.xlsx";
    
    // 匹配结果文件存放目录
    private static final String MATCH_RESULTS_DIR = "D:/jlh-saas/downloads/facebook/match-results/";
    
    /**
     * 分页查询Facebook数据
     */
    @PostMapping("/list")
    public Result getFacebookDataList(@RequestBody FacebookQueryDTO queryDTO) {
        // 获取当前用户ID
        Long userId = UserContext.getUserId();
        
        // 获取页码和每页大小
        int page = queryDTO.getPageNum() != null ? queryDTO.getPageNum() : 1;
        int size = queryDTO.getPageSize() != null ? queryDTO.getPageSize() : 10;


        // 第一页不扣除额度，免费查看
        if (page == 1) {
            log.info("查看的页码：{}，每页大小：{}", page, size);
            Page<FacebookUser> resultPage = facebookUserService.getFacebookDataPage(queryDTO);

            // 获取部分筛选条件
//            Map<String, List<String>> mapList = facebookUserService.getFacebookMapList();

            // 增加搜索次数统计
            workbenchStatsService.incrementFacebookSearchCount(userId);

            Map<String, Object> data = new HashMap<>();
            data.put("total", resultPage.getTotal());

            // 为每条记录添加id属性，使用phoneNumber作为id
            List<Map<String, Object>> records = getMaps(resultPage);

            // 添加详细日志，用于调试分页问题
            log.info("第一页查询结果: 总记录数={}, 当前页={}, 每页大小={}, 实际返回记录数={}",
                    resultPage.getTotal(), resultPage.getCurrent(), resultPage.getSize(), records.size());

            data.put("list", records);
//            data.put("currentLocations", mapList.get("currentLocations"));
//            data.put("hometowns", mapList.get("hometowns"));
//            data.put("relationshipStatuses", mapList.get("relationshipStatuses"));
//            data.put("workplaces", mapList.get("workplaces"));
            data.put("size", resultPage.getSize());
            data.put("current", resultPage.getCurrent());
            
            return Result.success(data);
        } else {
            // 检查用户余额
            UserBalance userBalance = userBalanceService.getByUserId(userId);
            if (userBalance == null || userBalance.getViewRecordsRemaining() < size) {
                return Result.error("您的查看记录余额不足，请充值后再查看");
            }
            
            // 查询数据
            Page<FacebookUser> resultPage = facebookUserService.getFacebookDataPage(queryDTO);

            // 扣除用户查看记录余额
            userBalanceService.deductViewRecords(userId, resultPage.getRecords().size());

            // 增加搜索次数统计
            workbenchStatsService.incrementFacebookSearchCount(userId);

            Map<String, Object> data = new HashMap<>();
            data.put("total", resultPage.getTotal());

            // 为每条记录添加id属性，使用phoneNumber作为id
            List<Map<String, Object>> records = getMaps(resultPage);

            // 添加详细日志，用于调试分页问题
            log.info("非第一页查询结果: 页码={}, 总记录数={}, 当前页={}, 每页大小={}, 实际返回记录数={}",
                    page, resultPage.getTotal(), resultPage.getCurrent(), resultPage.getSize(), records.size());

            data.put("list", records);
            data.put("size", resultPage.getSize());
            data.put("current", resultPage.getCurrent());
            
            return Result.success(data);
        }
    }

    @NotNull
    private List<Map<String, Object>> getMaps(Page<FacebookUser> resultPage) {

        List<Map<String, Object>> records = resultPage.getRecords().stream().map(record -> {
            Map<String, Object> map = new HashMap<>();
            map.put("id", record.getId()); // 使用phoneNumber作为前端的id
            map.put("phoneNumber", desensitization.maskPhone(record.getPhoneNumber()));
            map.put("facebookId", desensitization.maskPhone(record.getFacebookId()));
            map.put("firstName", record.getFirstName());
            map.put("lastName", record.getLastName());
            map.put("gender", record.getGender());
            map.put("currentLocation", record.getCurrentLocation());
            map.put("hometown", record.getHometown());
            map.put("relationshipStatus", record.getRelationshipStatus());
            map.put("workplace", record.getWorkplace());
            map.put("date", record.getDate());
            map.put("email", desensitization.maskEmail(record.getEmail()));
            map.put("mysqlTable", record.getMysqlTable());

            // 增加一个全名字段，方便前端显示
            String fullName = "";
            if (record.getFirstName() != null && !record.getFirstName().isEmpty()) {
                fullName += record.getFirstName();
            }
            if (record.getLastName() != null && !record.getLastName().isEmpty()) {
                if (!fullName.isEmpty()) {
                    fullName += " ";
                }
                fullName += record.getLastName();
            }
            map.put("fullName", fullName);

            return map;
        }).collect(Collectors.toList());


        return records;
    }

    /**
     * 获取Facebook联系人详情（通过数据表和Facebook ID）
     */
    @PostMapping("/detail")
    public Result getFacebookDetailByTableAndId(@RequestBody Map<String, String> params) {
        String mysqlTable = params.get("mysqlTable");
        String id = params.get("id");

        // 参数验证
        if (mysqlTable == null || mysqlTable.trim().isEmpty()) {
            return Result.error("mysqlTable参数不能为空");
        }
        if (id == null || id.trim().isEmpty()) {
            return Result.error("id参数不能为空");
        }

        try {

            // 查看当前用户的额度
            long userId = UserContext.getUserId();
            UserBalance userBalance = userBalanceService.getByUserId(userId);

            if (userBalance == null){
                return Result.error("用户不存在，请查看是否登录！");
            }

            if (userBalance.getViewRecordsRemaining() < 1){
                return Result.error("您的查看记录余额不足，请充值后再查看");
            }

            // 调用服务层方法获取详细信息
            FacebookUser data = facebookUserService.getFacebookDetailByTableAndId(mysqlTable, id);
            if (data == null) {
                return Result.error("未找到对应的联系人信息");
            }

            // 扣除用户查看记录余额
            userBalanceService.deductViewRecords(userId, 1);

            // 增加搜索次数统计
            workbenchStatsService.incrementFacebookSearchCount(userId);

            log.info("FacebookFacesController.getFacebookDetailByTableAndId 查询到的详情: {}",
                    JSON.toJSON(data));
            return Result.success(data);
        } catch (Exception e) {
            log.error("获取Facebook联系人详情失败: mysqlTable={}, id={}, error={}",
                     mysqlTable, id, e.getMessage(), e);
            return Result.error("获取联系人详情失败：" + e.getMessage());
        }
    }

    /**
     * 获取符合条件的记录总数
     */
    @PostMapping("/count")
    public Result countFacebookData(@RequestBody FacebookQueryDTO queryDTO) {
        log.info("计算符合条件的Facebook数据数量，参数: {}", queryDTO);
        
        long count = facebookUserService.countFacebookData(queryDTO);
        
        // 限制最大返回数量为50000
        if (count > 50000) {
            count = 50000;
        }
        
        Map<String, Object> result = new HashMap<>();
        result.put("count", count);
        return Result.success(result);
    }

    /**
     * 导出Facebook数据
     */
    @PostMapping("/export")
    public void exportFacebookData(
            HttpServletResponse response,
            @RequestBody FacebookQueryDTO queryDTO,
            @RequestParam(required = true) Integer exportCount) {
        log.info("导出Facebook数据，参数: {}, 导出数量: {}", queryDTO, exportCount);
        try {
            // 获取当前用户ID
            Long userId = UserContext.getUserId();
            
            // 检查用户余额
            UserBalance userBalance = userBalanceService.getByUserId(userId);
            if (userBalance == null || userBalance.getExportQuotaRemaining() < exportCount) {
                response.setContentType("application/json;charset=UTF-8");
                response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
                response.getWriter().write("{\"message\":\"您的导出次数余额不足，请充值后再导出\"}");
                return;
            }
            
            // 限制最大导出数量为50000
            if (exportCount > 50000) {
                exportCount = 50000;
            }
            
            // 设置导出限制
            queryDTO.setLimit(exportCount);

            // 性能监控：记录导出开始时间
            long exportStartTime = System.currentTimeMillis();
            log.info("开始两阶段查询导出，导出数量: {}, 查询条件: {}", exportCount, queryDTO);

            // 添加响应头以支持长连接
            response.setHeader("Connection", "Keep-Alive");
            response.setHeader("Proxy-Connection", "Keep-Alive");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            String fileName = "Facebook联系人数据_" + System.currentTimeMillis() + ".xlsx";
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name())
                .replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodedFileName);

            // 使用两阶段查询获取数据
            List<FacebookUser> dataList = facebookUserService.exportFacebookDataTwoStage(queryDTO);

            // 性能监控：记录导出完成时间
            long exportEndTime = System.currentTimeMillis();
            long exportDuration = exportEndTime - exportStartTime;
            double avgTimePerRecord = dataList.isEmpty() ? 0 : (double) exportDuration / dataList.size();

            log.info("两阶段查询导出完成 - 总耗时: {}ms, 平均每条: {:.2f}ms, 查询到 {} 条数据准备导出",
                    exportDuration, avgTimePerRecord, dataList.size());
            
            if (dataList.isEmpty()) {
                response.setContentType("application/json;charset=UTF-8");
                response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
                response.getWriter().write("{\"message\":\"没有找到符合条件的数据\"}");
                return;
            }
            
            // 扣除用户导出次数余额
            userBalanceService.deductExportQuota(userId, dataList.size());
            
            // 增加导出次数统计
            workbenchStatsService.incrementFacebookExportCount(userId, dataList.size());
            
            // 设置导出文件名和表头
            String[] columnNames = {
                "电话号码", "Facebook ID", "名字", "姓氏", 
                "性别", "当前所在地", "家乡", "关系状态", 
                "工作地点", "日期", "邮箱"
            };
            String[] fields = {
                "phoneNumber", "facebookId", "firstName", "lastName", 
                "gender", "currentLocation", "hometown", "relationshipStatus", 
                "workplace", "date", "email"
            };
            
            // 获取当前时间戳作为文件名一部分
            String filePath = "/export/" + fileName;
            
            // 计算文件大小（以KB为单位）
            String fileSize = FileUtils.estimateExcelSize(dataList.size()) + "KB";
            
            // 保存导出记录
            exportRecordService.addExportRecord(
                userId,
                fileName,
                "facebook", // 数据来源
                fileSize,
                dataList.size(),
                filePath
            );
            
            // 根据数据量选择不同的导出方式
            log.info("开始导出Excel文件，数据量: {}", dataList.size());
            if (dataList.size() > 10000) {
                // 大数据量使用优化版导出
                log.info("数据量大于10000，使用优化的Excel导出方式");
                ExcelUtils.exportExcelWithHighlightOptimized(
                    response, dataList, "Facebook联系人数据", columnNames, fields, null);
            } else {
                // 小数据量使用普通导出
                log.info("数据量小于等于10000，使用标准Excel导出方式");
                ExcelUtils.exportExcelWithHighlight(
                    response, dataList, "Facebook联系人数据", columnNames, fields, null);
            }
            log.info("成功导出Facebook数据");
            
        } catch (Exception e) {
            log.error("导出Facebook数据失败", e);
            try {
                // 在尝试重置响应之前先检查响应是否已提交
                if (!response.isCommitted()) {
                response.reset();
                response.setContentType("application/json;charset=UTF-8");
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.getWriter().write("{\"message\":\"导出数据失败: " + e.getMessage() + "\"}");
                } else {
                    log.warn("无法发送错误响应，响应已提交");
                    // 对于已提交的响应，不要尝试写入，只记录错误
                }
            } catch (IOException ex) {
                log.error("写入错误响应失败", ex);
            }
        }
    }
    
    /**
     * 关键词搜索API
     */
    @GetMapping("/search")
    public Result search(@RequestParam(required = false) String keyword,
                        @RequestParam(defaultValue = "0") int page,
                        @RequestParam(defaultValue = "10") int size,
                        @RequestParam(required = false) String gender,
                        @RequestParam(required = false) String location,
                        @RequestParam(required = false) String currentLocation,
                        @RequestParam(required = false) String hometown,
                        @RequestParam(required = false) String relationshipStatus,
                        @RequestParam(required = false) String workplace,
                        @RequestParam(required = false) String contactMethod) {
        
        // 获取当前用户ID
        Long userId = UserContext.getUserId();
        
        // 第一页不扣除额度，免费查看
        if (page == 0) {
            int offset = page * size;
            
            // 创建查询参数对象
            FacebookQueryDTO queryDTO = new FacebookQueryDTO();
            queryDTO.setPageNum(page + 1);
            queryDTO.setPageSize(size);
            queryDTO.setName(keyword);
            queryDTO.setGender(gender);
            queryDTO.setLocation(location);
            queryDTO.setCurrentLocation(currentLocation);
            queryDTO.setHometown(hometown);
            queryDTO.setRelationshipStatus(relationshipStatus);
            queryDTO.setWorkplace(workplace);
            queryDTO.setContactMethod(contactMethod);
            
            // 使用查询DTO进行高级搜索
            Page<FacebookUser> resultPage = facebookUserService.searchWithFilters(queryDTO, offset, size);
            long total = resultPage.getTotal();
            
            // 增加搜索次数统计
            workbenchStatsService.incrementFacebookSearchCount(userId);
            
            // 添加全名字段
            List<Map<String, Object>> formattedRecords = getMaps(resultPage);

            Map<String, Object> data = new HashMap<>();
            data.put("total", total);
            data.put("list", formattedRecords);
            data.put("size", size);
            data.put("current", page);
            
            return Result.success(data);
        } else {
            // 检查用户余额
            UserBalance userBalance = userBalanceService.getByUserId(userId);
            if (userBalance == null || userBalance.getViewRecordsRemaining() < size) {
                return Result.error("您的查看记录余额不足，请充值后再查看");
            }
            
            int offset = page * size;
            
            // 创建查询参数对象
            FacebookQueryDTO queryDTO = new FacebookQueryDTO();
            queryDTO.setPageNum(page + 1);
            queryDTO.setPageSize(size);
            queryDTO.setName(keyword);
            queryDTO.setGender(gender);
            queryDTO.setLocation(location);
            queryDTO.setCurrentLocation(currentLocation);
            queryDTO.setHometown(hometown);
            queryDTO.setRelationshipStatus(relationshipStatus);
            queryDTO.setWorkplace(workplace);
            queryDTO.setContactMethod(contactMethod);
            
            // 使用查询DTO进行高级搜索
            Page<FacebookUser> resultPage = facebookUserService.searchWithFilters(queryDTO, offset, size);
            
            // 扣除用户查看记录余额
            userBalanceService.deductViewRecords(userId, Math.toIntExact(resultPage.getSize()));
            
            // 增加搜索次数统计
            workbenchStatsService.incrementFacebookSearchCount(userId);
            
            long total = resultPage.getTotal();
            
            // 添加全名字段
            List<Map<String, Object>> formattedRecords = getMaps(resultPage);

            Map<String, Object> data = new HashMap<>();
            data.put("total", total);
            data.put("list", formattedRecords);
            data.put("size", size);
            data.put("current", page);
            
            return Result.success(data);
        }
    }
    
    /**
     * 关键词自动补全建议
     */
    @GetMapping("/suggestions")
    public Result getSuggestions(@RequestParam String keyword) {
        List<String> suggestions = facebookUserService.getSuggestions(keyword);
        return Result.success(suggestions);
    }

    /**
     * 下载匹配模板文件
     */
    @GetMapping("/download-template")
    public void downloadTemplate(
            HttpServletResponse response,
            @RequestParam(defaultValue = "default") String type) {
        log.info("下载匹配模板文件，类型: {}", type);
        try {
            // 获取模板文件路径
            String templatePath = getTemplatePath(type);
            String fileName = getTemplateFileName(type);
            
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));
            
            // 读取并输出文件
            String resourcePath = ResourceUtils.getURL("classpath:").getPath();
            resourcePath = resourcePath.startsWith("/") ? resourcePath.substring(1) : resourcePath;
            
            File file = new File(resourcePath + templatePath);
            if (!file.exists()) {
                log.error("模板文件不存在: {}", file.getAbsolutePath());
                response.setStatus(HttpServletResponse.SC_NOT_FOUND);
                response.getWriter().write("模板文件不存在，请先使用FacebookTemplateGeneratorRunner生成模板");
                return;
            }
            
            // 复制文件内容到响应输出流
            Files.copy(file.toPath(), response.getOutputStream());
            response.getOutputStream().flush();
            
            log.info("模板文件下载成功: {}", fileName);
        } catch (Exception e) {
            log.error("下载模板文件失败", e);
            try {
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.getWriter().write("下载模板文件失败: " + e.getMessage());
            } catch (IOException ex) {
                log.error("设置错误响应失败", ex);
            }
        }
    }
    
    /**
     * 根据模板类型获取模板文件路径
     */
    private String getTemplatePath(String type) {
        switch (type) {
            case "company_name_to_email":
                return TEMPLATE_DIR + COMPANY_NAME_TO_EMAIL;
            case "company_name_to_phone":
                return TEMPLATE_DIR + COMPANY_NAME_TO_PHONE;
            case "name_email_to_phone":
                return TEMPLATE_DIR + NAME_EMAIL_TO_PHONE;
            case "phone_name_to_email":
                return TEMPLATE_DIR + PHONE_NAME_TO_EMAIL;
            default:
                return TEMPLATE_DIR + DEFAULT_TEMPLATE;
        }
    }
    
    /**
     * 获取模板文件名
     */
    private String getTemplateFileName(String type) {
        switch (type) {
            case "company_name_to_email":
                return "工作地点姓名匹配邮箱模板.xlsx";
            case "company_name_to_phone":
                return "工作地点姓名匹配手机号模板.xlsx";
            case "name_email_to_phone":
                return "姓名邮箱匹配手机号模板.xlsx";
            case "phone_name_to_email":
                return "手机号姓名匹配邮箱模板.xlsx";
            default:
                return "匹配模板.xlsx";
        }
    }
    
    /**
     * 处理Facebook数据匹配
     */
    @PostMapping("/match")
    public Result matchFacebookData(
            @RequestParam("file") MultipartFile file,
            @RequestParam(defaultValue = "1") Integer matchType) {
        log.info("处理Facebook数据匹配，类型: {}", matchType);
        
        // 获取当前用户ID
        Long userId = UserContext.getUserId();
        
        try {
            if (file.isEmpty()) {
                return Result.error("请选择要上传的Excel文件");
            }
            
            // 检查文件类型
            String fileName = file.getOriginalFilename();
            if (fileName == null || (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls"))) {
                return Result.error("文件格式不正确，只支持.xlsx和.xls格式");
            }
            
            // 检查用户余额
            UserBalance userBalance = userBalanceService.getByUserId(userId);
            if (userBalance == null) {
                return Result.error("用户余额信息不存在，请联系管理员");
            }
            
            // 读取Excel文件
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            int totalRows = sheet.getPhysicalNumberOfRows();
            
            if (totalRows <= 1) { // 只有表头或为空
                workbook.close();
                return Result.error("上传的Excel文件没有数据");
            }
            
            // 获取表头
            Row headerRow = sheet.getRow(0);
            Map<String, Integer> headerMap = new HashMap<>();
            for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    headerMap.put(cell.getStringCellValue(), i);
                }
            }
            
            // 验证必要的列
            String[] requiredColumns;
            switch (matchType) {
                case 1: // 企业+姓名匹配邮箱
                    requiredColumns = new String[]{"FirstName", "LastName", "Workplace"};
                    break;
                case 2: // 企业+姓名匹配手机号
                    requiredColumns = new String[]{"FirstName", "LastName", "Workplace"};
                    break;
                case 3: // 姓名+邮箱匹配手机号
                    requiredColumns = new String[]{"FirstName", "LastName", "Email"};
                    break;
                case 4: // 手机号+姓名匹配邮箱
                    requiredColumns = new String[]{"PhoneNumber", "FirstName", "LastName"};
                    break;
                default:
                    requiredColumns = new String[]{"FirstName", "LastName"};
            }
            
            for (String column : requiredColumns) {
                if (!headerMap.containsKey(column)) {
                    workbook.close();
                    return Result.error("Excel文件缺少必要的列: " + column);
                }
            }
            
            // 准备匹配结果文件
            Workbook resultWorkbook = new XSSFWorkbook();
            Sheet resultSheet = resultWorkbook.createSheet("匹配结果");
            
            // 创建表头 - 使用数据库中的确切字段名
            Row resultHeaderRow = resultSheet.createRow(0);
            String[] headers = {
                "PhoneNumber", "FacebookID", "FirstName", "LastName", "Gender", 
                "CurrentLocation", "Hometown", "RelationshipStatus", "Workplace", 
                "Date", "Email", "匹配结果"
            };
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = resultHeaderRow.createCell(i);
                cell.setCellValue(headers[i]);
            }
            
            // 开始匹配处理
            int totalRecords = totalRows - 1; // 减去表头行
            int matchedCount = 0;

            // 性能监控：记录匹配开始时间
            long matchStartTime = System.currentTimeMillis();
            log.info("开始两阶段查询匹配，总记录数: {}, 匹配类型: {}", totalRecords, matchType);
            
            // 检查用户余额是否足够
            if (userBalance.getViewRecordsRemaining() < totalRecords || userBalance.getExportQuotaRemaining() < totalRecords) {
                workbook.close();
                return Result.error("您的余额不足，请充值后再试。当前匹配需要: " + totalRecords + 
                        ", 查看余额: " + userBalance.getViewRecordsRemaining() + 
                        ", 导出余额: " + userBalance.getExportQuotaRemaining());
            }
            
            // 逐行处理数据
            for (int i = 1; i < totalRows; i++) {
                Row dataRow = sheet.getRow(i);
                if (dataRow == null) continue;
                
                // 创建结果行
                Row resultRow = resultSheet.createRow(i);
                
                // 根据匹配类型执行不同的匹配逻辑
                FacebookUser matchedUser = null;
                String matchResult = "未匹配";
                
                try {
                    switch (matchType) {
                        case 1: // 企业+姓名匹配邮箱
                            String firstName1 = getCellValueAsString(dataRow, headerMap.get("FirstName"));
                            String lastName1 = getCellValueAsString(dataRow, headerMap.get("LastName"));
                            String workplace1 = getCellValueAsString(dataRow, headerMap.get("Workplace"));

                            if (isNotEmpty(firstName1) && isNotEmpty(lastName1) && isNotEmpty(workplace1)) {
                                // 使用两阶段查询优化性能
                                matchedUser = facebookUserService.matchByNameAndWorkplaceTwoStage(firstName1, lastName1, workplace1);
                            }
                            break;

                        case 2: // 企业+姓名匹配手机号
                            String firstName2 = getCellValueAsString(dataRow, headerMap.get("FirstName"));
                            String lastName2 = getCellValueAsString(dataRow, headerMap.get("LastName"));
                            String workplace2 = getCellValueAsString(dataRow, headerMap.get("Workplace"));

                            if (isNotEmpty(firstName2) && isNotEmpty(lastName2) && isNotEmpty(workplace2)) {
                                // 使用两阶段查询优化性能
                                matchedUser = facebookUserService.matchPhoneByNameAndWorkplaceTwoStage(firstName2, lastName2, workplace2);
                            }
                            break;

                        case 3: // 姓名+邮箱匹配手机号
                            String firstName3 = getCellValueAsString(dataRow, headerMap.get("FirstName"));
                            String lastName3 = getCellValueAsString(dataRow, headerMap.get("LastName"));
                            String email3 = getCellValueAsString(dataRow, headerMap.get("Email"));

                            if (isNotEmpty(firstName3) && isNotEmpty(lastName3) && isNotEmpty(email3)) {
                                // 使用两阶段查询优化性能
                                matchedUser = facebookUserService.matchPhoneByNameAndEmailTwoStage(firstName3, lastName3, email3);
                            }
                            break;

                        case 4: // 手机号+姓名匹配邮箱
                            String phoneNumber4 = getCellValueAsString(dataRow, headerMap.get("PhoneNumber"));
                            String firstName4 = getCellValueAsString(dataRow, headerMap.get("FirstName"));
                            String lastName4 = getCellValueAsString(dataRow, headerMap.get("LastName"));

                            if (isNotEmpty(phoneNumber4) && isNotEmpty(firstName4) && isNotEmpty(lastName4)) {
                                // 使用两阶段查询优化性能
                                matchedUser = facebookUserService.matchEmailByPhoneAndNameTwoStage(phoneNumber4, firstName4, lastName4);
                            }
                            break;
                    }
                    
                    // 如果匹配成功，填充结果行并扣除额度
                    if (matchedUser != null) {
                        // 填充匹配结果行
                        resultRow.createCell(0).setCellValue(matchedUser.getPhoneNumber() != null ? matchedUser.getPhoneNumber() : "");
                        resultRow.createCell(1).setCellValue(matchedUser.getFacebookId() != null ? matchedUser.getFacebookId() : "");
                        resultRow.createCell(2).setCellValue(matchedUser.getFirstName() != null ? matchedUser.getFirstName() : "");
                        resultRow.createCell(3).setCellValue(matchedUser.getLastName() != null ? matchedUser.getLastName() : "");
                        resultRow.createCell(4).setCellValue(matchedUser.getGender() != null ? matchedUser.getGender() : "");
                        resultRow.createCell(5).setCellValue(matchedUser.getCurrentLocation() != null ? matchedUser.getCurrentLocation() : "");
                        resultRow.createCell(6).setCellValue(matchedUser.getHometown() != null ? matchedUser.getHometown() : "");
                        resultRow.createCell(7).setCellValue(matchedUser.getRelationshipStatus() != null ? matchedUser.getRelationshipStatus() : "");
                        resultRow.createCell(8).setCellValue(matchedUser.getWorkplace() != null ? matchedUser.getWorkplace() : "");
                        resultRow.createCell(9).setCellValue(matchedUser.getDate() != null ? matchedUser.getDate().toString() : "");
                        resultRow.createCell(10).setCellValue(matchedUser.getEmail() != null ? matchedUser.getEmail() : "");
                        resultRow.createCell(11).setCellValue("匹配成功");
                        
                        // 扣除用户额度（每条成功匹配扣除一点查看余额和一点导出余额）
                        userBalanceService.deductViewRecords(userId, 1);
                        userBalanceService.deductExportQuota(userId, 1);
                        
                        matchedCount++;
                        matchResult = "匹配成功";
                    } else {
                        // 匹配失败，填充原始数据
                        for (String header : headers) {
                            if (headerMap.containsKey(header)) {
                                int colIndex = headerMap.get(header);
                                String cellValue = getCellValueAsString(dataRow, colIndex);
                                int resultColIndex = getHeaderIndex(headers, header);
                                if (resultColIndex >= 0) {
                                    resultRow.createCell(resultColIndex).setCellValue(cellValue);
                                }
                            }
                        }
                        resultRow.createCell(11).setCellValue("未匹配");
                    }
                } catch (Exception e) {
                    log.error("处理第" + i + "行数据时发生异常", e);
                    // 匹配异常，填充原始数据
                    for (String header : headers) {
                        if (headerMap.containsKey(header)) {
                            int colIndex = headerMap.get(header);
                            String cellValue = getCellValueAsString(dataRow, colIndex);
                            int resultColIndex = getHeaderIndex(headers, header);
                            if (resultColIndex >= 0) {
                                resultRow.createCell(resultColIndex).setCellValue(cellValue);
                            }
                        }
                    }
                    resultRow.createCell(11).setCellValue("匹配异常");
                }
            }
            
            // 性能监控：记录匹配完成时间和性能指标
            long matchEndTime = System.currentTimeMillis();
            long totalMatchTime = matchEndTime - matchStartTime;
            double avgTimePerRecord = totalRecords > 0 ? (double) totalMatchTime / totalRecords : 0;
            double matchSuccessRate = totalRecords > 0 ? (double) matchedCount / totalRecords * 100 : 0;

            log.info("两阶段查询匹配完成 - 总耗时: {}ms, 平均每条: {:.2f}ms, 匹配成功率: {:.1f}% ({}/{})",
                    totalMatchTime, avgTimePerRecord, matchSuccessRate, matchedCount, totalRecords);

            // 记录工作台统计
            workbenchStatsService.incrementFacebookSearchCount(userId);
            workbenchStatsService.incrementFacebookExportCount(userId, matchedCount);
            
            // 自动适应列宽
            for (int i = 0; i < headers.length; i++) {
                resultSheet.autoSizeColumn(i);
            }
            
            // 创建单元格样式
            CellStyle headerStyle = resultWorkbook.createCellStyle();
            Font headerFont = resultWorkbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            
            // 应用样式到表头
            for (int i = 0; i < headers.length; i++) {
                Cell cell = resultHeaderRow.getCell(i);
                if (cell != null) {
                    cell.setCellStyle(headerStyle);
                }
            }
            
            // 生成结果文件名
            String resultFileName = "匹配结果_" + matchType + "_" + System.currentTimeMillis() + ".xlsx";
            
            // 保存结果文件到永久路径
            String resultFilePath = saveResultFile(resultWorkbook, userId, matchType, resultFileName);
            
            // 关闭工作簿
            workbook.close();
            resultWorkbook.close();
            
            // 保存匹配记录到数据库
            FacebookMatchRecord record = new FacebookMatchRecord();
            record.setUserId(userId);
            record.setMatchType(matchType);
            record.setFilePath(resultFilePath);
            record.setTotalRecords(totalRecords);
            record.setMatchedRecords(matchedCount);
            record.setCreateTime(new Date());
            facebookMatchRecordService.save(record);
            
            // 生成文件下载URL - 使用记录ID作为参数
            String fileUrl = "/facebook/download-history-result?recordId=" + record.getId();
            
            // 返回匹配结果
            Map<String, Object> result = new HashMap<>();
            result.put("total", totalRecords);
            result.put("matched", matchedCount);
            result.put("fileUrl", fileUrl);
            result.put("recordId", record.getId());
            
            return Result.success(result);
        } catch (Exception e) {
            log.error("Facebook数据匹配失败", e);
            return Result.error("匹配失败: " + e.getMessage());
        }
    }

    /**
     * 预估导出数据量（两阶段查询优化）
     */
    @PostMapping("/export/estimate")
    public Result estimateExportCount(@RequestBody FacebookQueryDTO queryDTO) {
        log.info("预估导出数据量，参数: {}", queryDTO);

        try {
            // 获取当前用户ID
            Long userId = UserContext.getUserId();

            // 性能监控：记录预估开始时间
            long estimateStartTime = System.currentTimeMillis();

            // 使用两阶段查询获取数据总数
            long totalCount = facebookUserService.getExportDataCountTwoStage(queryDTO);

            // 性能监控：记录预估完成时间
            long estimateEndTime = System.currentTimeMillis();
            long estimateDuration = estimateEndTime - estimateStartTime;

            log.info("两阶段查询预估完成 - 总耗时: {}ms, 预估数据量: {}", estimateDuration, totalCount);

            // 检查用户余额
            UserBalance userBalance = userBalanceService.getByUserId(userId);
            long availableQuota = userBalance != null ? userBalance.getExportQuotaRemaining() : 0;

            // 构建响应数据
            Map<String, Object> result = new HashMap<>();
            result.put("totalCount", totalCount);
            result.put("availableQuota", availableQuota);
            result.put("canExport", totalCount <= availableQuota);
            result.put("estimateDuration", estimateDuration);

            if (totalCount > availableQuota) {
                result.put("message", String.format("预估导出%d条数据，但您的余额只有%d条，请充值后再导出", totalCount, availableQuota));
            } else {
                result.put("message", String.format("预估导出%d条数据，您的余额充足", totalCount));
            }

            return Result.success(result);

        } catch (Exception e) {
            log.error("预估导出数据量失败", e);
            return Result.error("预估导出数据量失败: " + e.getMessage());
        }
    }

    /**
     * 获取匹配历史记录
     */
    @GetMapping("/match-records")
    public Result getMatchRecords(
            @RequestParam(defaultValue = "1") Integer pageNum,
            @RequestParam(defaultValue = "10") Integer pageSize) {
        Long userId = UserContext.getUserId();
        try {
            Page<FacebookMatchRecord> page = facebookMatchRecordService.getUserMatchRecords(userId, pageNum, pageSize);
            return Result.success(page);
        } catch (Exception e) {
            log.error("获取匹配历史记录失败", e);
            return Result.error("获取匹配历史记录失败: " + e.getMessage());
        }
    }
    
    /**
     * 下载历史匹配结果文件
     */
    @GetMapping("/download-history-result")
    public void downloadHistoryResult(
            HttpServletResponse response,
            @RequestParam Long recordId) {
        try {
            // 获取当前用户ID
            Long userId = UserContext.getUserId();
            
            // 获取匹配记录
            FacebookMatchRecord record = facebookMatchRecordService.getById(recordId);
            
            // 检查记录是否存在
            if (record == null) {
                response.setStatus(HttpServletResponse.SC_NOT_FOUND);
                response.getWriter().write("匹配记录不存在");
                return;
            }
            
            // 检查权限 - 只能下载自己的匹配记录
            if (!record.getUserId().equals(userId)) {
                response.setStatus(HttpServletResponse.SC_FORBIDDEN);
                response.getWriter().write("无权访问此匹配记录");
                return;
            }
            
            // 使用文件工具类下载文件
            FileUtils.downloadFile(response, record.getFilePath());
        } catch (Exception e) {
            log.error("下载历史匹配结果失败", e);
            try {
                response.reset();
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                response.setContentType("text/plain;charset=UTF-8");
                response.getWriter().write("下载历史匹配结果失败: " + e.getMessage());
            } catch (IOException ex) {
                log.error("设置错误响应失败", ex);
            }
        }
    }

    /**
     * 获取单元格的字符串值
     */
    private String getCellValueAsString(Row row, int columnIndex) {
        if (columnIndex < 0 || columnIndex >= row.getLastCellNum()) {
            return "";
        }
        
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue().toString();
                } else {
                    // 将数字转为字符串，避免科学计数法
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception ex) {
                        return "";
                    }
                }
            default:
                return "";
        }
    }

    /**
     * 检查字符串是否为空
     */
    private boolean isNotEmpty(String str) {
        return str != null && !str.trim().isEmpty();
    }

    /**
     * 获取头部索引
     */
    private int getHeaderIndex(String[] headers, String headerName) {
        for (int i = 0; i < headers.length; i++) {
            if (headers[i].equals(headerName)) {
                return i;
            }
        }
        return -1;
    }
    
    // 保存匹配结果文件到永久存储位置
    private String saveResultFile(Workbook workbook, Long userId, int matchType, String fileName) throws IOException {
        // 确保目录存在
        File storageDir = new File(MATCH_RESULTS_DIR);
        if (!storageDir.exists()) {
            storageDir.mkdirs();
        }
        
        // 生成文件名
        String resultFileName = userId + "_" + matchType + "_" + System.currentTimeMillis() + ".xlsx";
        String storagePath = MATCH_RESULTS_DIR + resultFileName;
        
        // 保存文件
        try (FileOutputStream out = new FileOutputStream(storagePath)) {
            workbook.write(out);
            out.flush();
        }
        
        File resultFile = new File(storagePath);
        if (!resultFile.exists() || resultFile.length() == 0) {
            throw new IOException("结果文件创建失败或为空: " + storagePath);
        }
        
        log.info("成功保存匹配结果文件: {}，文件大小: {}字节", storagePath, resultFile.length());
        return storagePath;
    }


    /**
     * 搜索当前所在地
     */
    @GetMapping("/locations/current")
    public Result searchCurrent(
            @RequestParam String keyword) {
        List<String> industries = facebookUserService.searchCurrent(keyword);

        return Result.success(industries);
    }

    /**
     * 搜索城镇
     */
    @GetMapping("/locations/hometown")
    public Result searchHometown(
            @RequestParam String keyword) {
        List<String> hometowns = facebookUserService.searchHometown(keyword);

        return Result.success(hometowns);
    }

    /**
     * 搜索工作地点
     */
    @GetMapping("/workplaces")
    public Result searchWorkplaces(
            @RequestParam String keyword) {
        List<String> workplaces = facebookUserService.searchWorkplaces(keyword);

        return Result.success(workplaces);
    }
} 